package database;

import java.util.Map;
import java.util.Set;

import worst.shopping.WorstShoppingApplication;
import android.app.Application;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

public class ListDAO {
	private SQLiteDatabase _dataDataBase;
	
	public ListDAO(Application application)
	{
		WorstShoppingApplication worstApplication = (WorstShoppingApplication)application;
		_dataDataBase = worstApplication._databaseAdapter.getReadableDatabase();
	}
	
	public void insertList(String listName) {
		ContentValues cValues = new ContentValues();
		cValues.put(DataBaseKeys.LIST_NAME, listName);

		_dataDataBase.insert(DataBaseKeys.LIST_TABLE, null, cValues);
	}
	
	public Cursor getAllListNamesCursor()
	{
		return _dataDataBase.query(true, DataBaseKeys.LIST_TABLE, new String[]{ "rowid _id", DataBaseKeys.LIST_NAME }, null, null, null, null, null, null);
	}
	
	public void deleteList(String listToDelete) {
		_dataDataBase.delete(DataBaseKeys.LIST_TABLE, DataBaseKeys.LIST_NAME+" = ?", new String[]{ listToDelete });
		_dataDataBase.delete(DataBaseKeys.DATA_TABLE, DataBaseKeys.LIST_NAME+" = ?", new String[]{ listToDelete });
	}
	
	public void renameList(String oldListName, String newListName)
	{
		//update list name in lists table
		ContentValues cValues = new ContentValues();
		cValues.put(DataBaseKeys.LIST_NAME, newListName);

		_dataDataBase.update(DataBaseKeys.LIST_TABLE, cValues, DataBaseKeys.LIST_NAME+" = ?", new String[]{ oldListName });
		
		//update list name in data table
		_dataDataBase.update(DataBaseKeys.DATA_TABLE, cValues, DataBaseKeys.LIST_NAME+" = ?", new String[]{ oldListName });
	}

	public void saveNewList(Map<String, Integer> list, String listname) {
		insertList(listname);
		
		Set< String > keySet = list.keySet();
		
		ContentValues contentValues = new ContentValues();
		_dataDataBase.beginTransaction();
		try
		{
			for (String string : keySet) {
				contentValues.clear();
				contentValues.put(DataBaseKeys.LIST_NAME, listname);
				contentValues.put(DataBaseKeys.PRODUCT_NAME, string);
				contentValues.put(DataBaseKeys.PRODUCT_COUNT, list.get(string));
			
				_dataDataBase.insert(DataBaseKeys.DATA_TABLE, null, contentValues);
			}
			_dataDataBase.setTransactionSuccessful();
		}catch (Exception e) {
			// TODO: handle exception
		}finally
		{
			_dataDataBase.endTransaction();
		}
	}

	public Cursor getItemsForList(String listname) {
		String query = "SELECT " + 
						DataBaseKeys.DATA_TABLE+".rowid _id, " +
						DataBaseKeys.PRODUCT_PRICE + ", " +
						DataBaseKeys.PRODUCT_UNIT + ", " +
						DataBaseKeys.PRODUCT_COUNT + ", " +
						DataBaseKeys.PRODUCT_NAME + ", " + 
						DataBaseKeys.PRODUCT_ICON + ", " +
						DataBaseKeys.PRODUCT_MARK +
						" FROM " + 
						DataBaseKeys.DATA_TABLE + 
						" NATURAL JOIN " + 
						DataBaseKeys.PRODUCT_TABLE + 
						" WHERE " + 
						DataBaseKeys.DATA_TABLE+
						"."
						+
						DataBaseKeys.LIST_NAME+" = ?" +
						" ORDER BY " +
						DataBaseKeys.PRODUCT_NAME + " ASC";
		return _dataDataBase.rawQuery(query, new String[]{ listname });
	}
	
	public boolean listExists(String listname) {
		
		Cursor cursor = _dataDataBase.query(DataBaseKeys.LIST_TABLE, new String[]{ DataBaseKeys.LIST_NAME }, DataBaseKeys.LIST_NAME + " = ?", new String[]{ listname }, null, null, null);
		boolean exists = cursor.moveToFirst();
		
		cursor.close();
		
		return exists;
	}
	
	public void removeListEntry(String listname, String productname)
	{
		_dataDataBase.delete(DataBaseKeys.DATA_TABLE, DataBaseKeys.LIST_NAME + "= ? AND "+DataBaseKeys.PRODUCT_NAME+"= ?", new String[]{listname, productname});
	}
	
	public void insertListEntry(String listname, String productname, String count)
	{
		ContentValues contentValues = new ContentValues();
		contentValues.put(DataBaseKeys.LIST_NAME, listname);
		contentValues.put(DataBaseKeys.PRODUCT_NAME, productname);
		contentValues.put(DataBaseKeys.PRODUCT_COUNT, count);
		contentValues.put(DataBaseKeys.PRODUCT_MARK, 0);
			
		_dataDataBase.insert(DataBaseKeys.DATA_TABLE, null, contentValues);		
	}
	
	public void updateListEntryCount(String listname, String productname, String count)
	{
		ContentValues contentValues = new ContentValues();
		contentValues.put(DataBaseKeys.PRODUCT_COUNT, count);
			
		_dataDataBase.update(DataBaseKeys.DATA_TABLE, contentValues,DataBaseKeys.LIST_NAME + "= ? AND "+DataBaseKeys.PRODUCT_NAME+"= ?", new String[]{listname, productname});	
	}
	
	public void markListEntry(String listname, String productname, boolean marked)
	{
		int mark = marked ? 1 : 0;
		
		ContentValues contentValues = new ContentValues();
		contentValues.put(DataBaseKeys.PRODUCT_MARK, mark);
		
		_dataDataBase.update(DataBaseKeys.DATA_TABLE, contentValues, DataBaseKeys.LIST_NAME + "= ? AND "+DataBaseKeys.PRODUCT_NAME+"= ?", new String[]{listname, productname});	
	}

	public boolean isSelected(String listname, String productName) {
		Cursor cursor = _dataDataBase.query(DataBaseKeys.DATA_TABLE, new String[]{ DataBaseKeys.PRODUCT_MARK }, DataBaseKeys.LIST_NAME + "= ? AND " + DataBaseKeys.PRODUCT_NAME + "= ?", new String[]{ listname, productName }, null, null, null);

		if(cursor.moveToFirst())
		{
			if(cursor.getInt(cursor.getColumnIndex(DataBaseKeys.PRODUCT_MARK)) == 1)
			{
				return true;
			}
		}
		return false;
	}

	public void beginTransaction() {
		_dataDataBase.beginTransaction();
	}
	
	public void setTransactionSuccessful(){
		_dataDataBase.setTransactionSuccessful();
	}
	
	public void endTransaction(){
		_dataDataBase.endTransaction();
	}
	
	public void insertIntoTemporaryTable(String productname, int count)
	{
		ContentValues contentValues = new ContentValues();
		contentValues.put(DataBaseKeys.PRODUCT_NAME, productname);
		contentValues.put(DataBaseKeys.PRODUCT_COUNT, count);
		
		_dataDataBase.insert(DataBaseKeys.TEMP_LIST_TABLE, null, contentValues);
	}
	
	public void removeFromTemporaryTable(String productname)
	{
		_dataDataBase.delete(DataBaseKeys.TEMP_LIST_TABLE, DataBaseKeys.PRODUCT_NAME + "= ?", new String[]{ productname });
	}
	
	public boolean isInTemporaryTable(String productname)
	{
		Cursor cursor = _dataDataBase.query(DataBaseKeys.TEMP_LIST_TABLE, new String[]{ DataBaseKeys.PRODUCT_NAME }, DataBaseKeys.PRODUCT_NAME + "= ?", new String[]{ productname }, null, null, null);
	
		return cursor.moveToFirst();
	}
	
	public int getCountOfProductInTemporaryTable(String productname)
	{
		Cursor cursor = _dataDataBase.query(DataBaseKeys.TEMP_LIST_TABLE, new String[]{ DataBaseKeys.PRODUCT_COUNT }, DataBaseKeys.PRODUCT_NAME + "= ?", new String[]{ productname }, null, null, null);
	
		if(cursor.moveToNext())
		{
			return cursor.getInt(cursor.getColumnIndex(DataBaseKeys.PRODUCT_COUNT));
		}
		else
		{
			return 0;
		}
	}
	
	public void clearTemporaryTable()
	{
		_dataDataBase.delete(DataBaseKeys.TEMP_LIST_TABLE, "", new String[]{});
	}

	public void updateTemporaryTable(String productname, int count) {
		ContentValues contentValues = new ContentValues();
		contentValues.put(DataBaseKeys.PRODUCT_COUNT, count);
		_dataDataBase.update(DataBaseKeys.TEMP_LIST_TABLE, contentValues, DataBaseKeys.PRODUCT_NAME + "= ?", new String[] { productname });
	}

	public void saveListFromTemporaryToPersistant(String listname) {
		//get all elements in temp table
		Cursor cursor = _dataDataBase.query(DataBaseKeys.TEMP_LIST_TABLE, new String[] { DataBaseKeys.PRODUCT_NAME, DataBaseKeys.PRODUCT_COUNT }, null, null, null, null, null);
		
		_dataDataBase.beginTransaction();
		try {
			if(cursor.moveToFirst())
			{
				ContentValues contentValues = new ContentValues();
				do
				{
					contentValues.clear();
					
					contentValues.put(DataBaseKeys.LIST_NAME, listname);
					contentValues.put(DataBaseKeys.PRODUCT_NAME, cursor.getString(cursor.getColumnIndex(DataBaseKeys.PRODUCT_NAME)));
					contentValues.put(DataBaseKeys.PRODUCT_COUNT, cursor.getString(cursor.getColumnIndex(DataBaseKeys.PRODUCT_COUNT)));
					contentValues.put(DataBaseKeys.PRODUCT_MARK, 0);

					_dataDataBase.insert(DataBaseKeys.DATA_TABLE, null, contentValues);
				}while(cursor.moveToNext());
				_dataDataBase.setTransactionSuccessful();
			}			
		} catch (Exception e) {
			// TODO: handle exception
		} finally
		{
			_dataDataBase.endTransaction();
		}	

	}

//	public void test(String listname) {
//		Cursor c  = _dataDataBase.rawQuery("SELECT * FROM " + DataBaseKeys.PRODUCT_TABLE + " LEFT JOIN "
//				+ DataBaseKeys.DATA_TABLE + " ON " + DataBaseKeys.PRODUCT_TABLE + "." + DataBaseKeys.PRODUCT_NAME +
//				" = " + DataBaseKeys.DATA_TABLE + "." + DataBaseKeys.PRODUCT_NAME + 
//				" WHERE " + DataBaseKeys.DATA_TABLE + "." + DataBaseKeys.LIST_NAME + " = ?", new String[]{listname});
//		
//		String cols = DataBaseKeys.PRODUCT_TABLE + "." + DataBaseKeys.PRODUCT_NAME + ", " +DataBaseKeys.PRODUCT_MARK;
//		
//		c = _dataDataBase.rawQuery(
//				"SELECT " + cols + " FROM " + DataBaseKeys.PRODUCT_TABLE + " LEFT JOIN "+
//				"(SELECT * FROM " + DataBaseKeys.DATA_TABLE + " WHERE " + DataBaseKeys.LIST_NAME + "= ?) AS TMP ON " + DataBaseKeys.PRODUCT_TABLE + "." + DataBaseKeys.PRODUCT_NAME +
//				" = TMP." + DataBaseKeys.PRODUCT_NAME, new String[]{listname});
//				
//		
//		for (String s : c.getColumnNames()) {
//			System.out.println(s);
//		}
//		while(c.moveToNext())
//		{
//			String s = c.getString(c.getColumnIndex(DataBaseKeys.PRODUCT_NAME));
//			System.out.println("-" + s);
//			String s2 = c.getString(c.getColumnIndex(DataBaseKeys.PRODUCT_MARK));
//			System.out.println("-" + s2);
//		}
//			
//		
//	}
}
